from sqlalchemy import and_, select
from sqlalchemy.orm import joinedload

from src.application.category.dto import CategoryDTO
from src.application.category.interfaces import CategoryReader, GetCategoriesFilters
from src.application.server.enums import ServerPrivacy, ServerStatus
from src.domain.common.constants import Empty
from src.infrastructure.db.converters.category import convert_db_model_to_category_dto
from src.infrastructure.db.exception_mapper import exception_mapper
from src.infrastructure.db.models import Category, Server
from src.infrastructure.db.repositories.base import SQLAlchemyRepo


class CategoryReaderImpl(SQLAlchemyRepo, CategoryReader):
    @exception_mapper
    async def filter_categories(self, filters: GetCategoriesFilters) -> list[CategoryDTO]:
        query = (select(Category).join(Category.servers)).filter(Server.status == ServerStatus.active)
        if filters.proxy_type != Empty.UNSET:
            query = query.filter(Server.proxy_type == filters.proxy_type)

        if filters.proxy_privacy != Empty.UNSET:
            query = query.filter(Server.proxy_privacy == filters.proxy_privacy)

        if filters.provider_id != Empty.UNSET:
            query = query.filter(Server.provider_id == filters.provider_id)

        if filters.country_id != Empty.UNSET:
            query = query.filter(Server.country_id == filters.country_id)

        res = await self._session.scalars(query)
        categories: list[Category] = list(res.unique().all())

        return [convert_db_model_to_category_dto(category) for category in categories]

    @exception_mapper
    async def get_category_by_id(self, category_id: int, joined: bool = False) -> CategoryDTO:
        query = select(Category).where(
            Category.id == category_id,
        )

        if joined:
            query = query.options(joinedload(Category.sites))

        category: Category | None = await self._session.scalar(query)

        return convert_db_model_to_category_dto(category)

    @exception_mapper
    async def get_category_by_name(self, category_name: str) -> CategoryDTO:
        category: Category | None = await self._session.scalar(select(Category).where(Category.name == category_name))

        return convert_db_model_to_category_dto(category)

    @exception_mapper
    async def get_category_for_private(self, country_id: int, provider_id: int) -> CategoryDTO:
        query = (
            select(Category)
            .join(Category.servers)
            .where(
                and_(
                    Server.proxy_privacy == ServerPrivacy.private,
                    Server.country_id == country_id,
                    Server.provider_id == provider_id,
                )
            )
        )

        category: Category | None = await self._session.scalar(query)

        return convert_db_model_to_category_dto(category)
